package com.googlecode.connectlet.util.db;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import com.googlecode.connectlet.util.ByteArrayQueue;
import com.googlecode.connectlet.util.Closeables;
import com.googlecode.connectlet.util.Logger;
import com.googlecode.connectlet.util.Streams;

public class DBExecutor {
	private static Object[] valueOf(int... values) {
		Object[] objs = new Object[values.length];
		for (int i = 0; i < values.length; i ++) {
			objs[i] = Integer.valueOf(values[i]);
		}
		return objs;
	}

	private static volatile DBExecutor instance = null;

	public static DBExecutor getInstance() {
		if (instance == null) {
			synchronized (DBExecutor.class) {
				if (instance == null) {
					instance = new DBExecutor(ConnectionPool.getInstance());
				}
			}
		}
		return instance;
	}

	private ConnectionPool connectionPool;

	public DBExecutor(ConnectionPool connectionPool) {
		this.connectionPool = connectionPool;
	}

	public int update(String sql, int... in) {
		return update(sql, valueOf(in));
	}

	public int update(boolean ignoreError, String sql, int... in) {
		return update(ignoreError, sql, valueOf(in));
	}

	public int update(int[] insertId, String sql, int... in) {
		return update(insertId, false, sql, valueOf(in));
	}

	public int update(int[] insertId, boolean ignoreError,
			String sql, int... in) {
		return update(insertId, ignoreError, sql, valueOf(in));
	}

	public int update(String sql, Object... in) {
		return update(null, false, sql, in);
	}

	public int update(boolean ignoreError, String sql, Object... in) {
		return update(null, ignoreError, sql, in);
	}

	public int update(int[] insertId, String sql, Object... in) {
		return update(insertId, false, sql, in);
	}

	public int update(int[] insertId, boolean ignoreError,
			String sql, Object... in) {
		Connection conn = connectionPool.borrowObject();
		if (conn == null) {
			Logger.severe("Connection refused");
			return -1;
		}
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			if (insertId != null && insertId.length > 0) {
				ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			} else {
				ps = conn.prepareStatement(sql);
			}
			for (int i = 0; i < in.length; i ++) {
				ps.setObject(i + 1, in[i]);
			}
			int numRows;
			if (ignoreError) {
				try {
					numRows = ps.executeUpdate();
				} catch (Exception e) {
					Logger.info(e.getMessage());
					return -1;
				}
			} else {
				numRows = ps.executeUpdate();
			}
			if (insertId != null && insertId.length > 0) {
				rs = ps.getGeneratedKeys();
				if (rs.next()) {
					insertId[0] = rs.getInt(1);
				}
			}
			return numRows;
		} catch (Exception e) {
			Logger.severe(e);
			return -1;
		} finally {
			Closeables.close(rs);
			Closeables.close(ps);
			connectionPool.returnObject(conn);
		}
	}

	public Row query(int columns, String sql, int... in) {
		return query(columns, sql, valueOf(in));
	}

	public boolean query(int columns, RowCallback callback,
			String sql, int... in) {
		return query(columns, callback, sql, valueOf(in));
	}

	public Row query(int columns, String sql, Object... in) {
		final Row[] row_ = new Row[1];
		row_[0] = null;
		RowCallback callback = new RowCallback() {
			@Override
			public boolean onRow(Row row) {
				row_[0] = row;
				return false;
			}
		};
		if (!query(columns, callback, sql, in)) {
			return Row.ERROR;
		}
		return (row_[0] == null ? Row.EMPTY : row_[0]);
	}

	public boolean query(int columns, RowCallback callback,
			String sql, Object... in) {
		Connection conn = connectionPool.borrowObject();
		if (conn == null) {
			Logger.severe("Connection refused");
			return false;
		}
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < in.length; i ++) {
				ps.setObject(i + 1, in[i]);
			}
			rs = ps.executeQuery();
			while (rs.next() && callback.onRow(new Row(rs, columns))) {/**/}
			return true;
		} catch (Exception e) {
			Logger.severe(e);
			return false;
		} finally {
			Closeables.close(rs);
			Closeables.close(ps);
			connectionPool.returnObject(conn);
		}
	}

	public void source(File sqlFile) {
		ByteArrayQueue baq = new ByteArrayQueue();
		try {
			FileInputStream inSql = new FileInputStream(sqlFile);
			Streams.copy(inSql, baq.getOutputStream());
			inSql.close();
		} catch (Exception e) {
			Logger.severe(e);
			throw new RuntimeException(e);
		}
		String[] sqls = baq.toString().split(";");
		for (String sql : sqls) {
			System.out.println(sql + ";");
			update(sql);
		}
	}
}